var express = require('express');
const myroute = express.Router()
const mySql = require('../model/index')
const upload = require('../utils/tupian')
//获取审批信息
myroute.get('/get_shenpi', function (request, response) {
    const sql = `SELECT *,application_type.type AS qing FROM application 
    LEFT JOIN application_type ON application.type = application_type.id 
    LEFT JOIN admin ON application.apply_name = admin.work_id 
    LEFT JOIN postadmin ON admin.jobs = postadmin.post_id 
    LEFT JOIN department ON postadmin.post_department = department.id
    WHERE jobs IN (
    SELECT post_id FROM postadmin WHERE superior IN (
    SELECT post_id FROM admin 
    LEFT JOIN postadmin ON admin.jobs=postadmin.post_id 
    WHERE account=${request.query.account}))`
    // console.log(request.query.account);
    mySql.query(sql, function (err, data) {
        if (err) {
            console.log(err);
        } else {
            if (data.length) {
                response.send({ code: 200, data: data })
            } else {
                response.send({ code: 203, msg: '暂无数据查询！' })
            }
        }
    })
})
//获取公告信息
myroute.get('/get_gonggao', function (request, response) {
    const sql = `SELECT * FROM notice
    LEFT JOIN notice_class ON notice.notice_class=notice_class.notice_classId`;
    mySql.query(sql, function (err, data) {
        if (err) {
            console.log(err);
        } else {
            if (data.length > 0) {
                response.send({ code: 200, data: data })
            } else {
                response.send({ code: 203, msg: '暂无公告' })
            }
        }
    })
})
//修改密码
myroute.post('/edit_pwd', function (request, response) {
    const sql1 = `SELECT * FROM admin WHERE account='${request.body.account}' AND PASSWORD='${request.body.jiu}'`
    const sql2 = `UPDATE admin SET PASSWORD='${request.body.xin}' WHERE account='${request.body.account}';`
    mySql.query(sql1, function (err1, data1) {
        if (err1) {
            console.log(err1);
        } else {
            if (data1) {
                mySql.query(sql2, function (err2, data2) {
                    if (err2) {
                        console.log(err2);
                    } else {
                        if (data2) {
                            response.send({ code: 200, msg: '修改成功！' })
                        } else {
                            response.send({ code: 203, msg: '修改失败！' })
                        }
                    }
                })
            } else {
                response.send({ code: 500, msg: '旧密码输入错误！' })
            }
        }
    })
})
//流水号
myroute.post('/apply_liushui', function (req, res) {
    const sql = `SELECT * FROM application WHERE mun='${req.body.mun}'`
    mySql.query(sql, function (err, data) {
        if (err) {
            console.log(err);
        } else {
            if (data.length > 0) {
                res.send({ code: 203, msg: '流水号已被占用！' })
            } else {
                res.send({ code: 200, msg: '流水号未被占用！' })
            }
        }
    })
})
//提交申请
myroute.post('/apply_shenqing', function (req, res) {
    const sql = ` INSERT INTO application VALUES(NULL,'${req.body.mun}','${req.body.type}',DEFAULT,'${req.body.user.work_id}','${req.body.starttime}','${req.body.content}','${req.body.endtime}','${req.body.applytime}',NULL,'${req.body.user.work_id}')`
    const sql2 = ` INSERT INTO message VALUES(NULL,'${req.body.user.name}','${req.body.user.name}于${req.body.applytime}发起一条申请，请您处理',DEFAULT,'${req.body.applytime}',DEFAULT)`
    mySql.query(sql, function (err, data) {
        if (err) {
            console.log(err);
        } else {
            if (data) {
                mySql.query(sql2, function (err2, data2) {
                    if (err2) {
                        console.log(err2);
                    } else {
                        if (data2) {
                            res.send({ code: 200, msg: '已提交申请，请关注上级回复！' })
                        }
                    }
                })
            } else {
                res.send({ code: 203, msg: '提交申请失败！' })
            }
        }
    })
})

//刘锦昌写的，小程序获取当前部门的上下班时间
myroute.get('/bumen_time', (req, res) => {
    const sql = `SELECT department.department_name,work_time.colony,work_time.forenoon,work_time.afternoon
                FROM department
                LEFT JOIN work_time ON work_time.department=department.id
                WHERE department.department_name = "${req.query.bumen}"
                AND work_time.colony="${req.query.jiequ2}";`;
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err);
        } else {
            if(data){
                console.log("上下班有数据");
                res.send({ code: 200, data: data })
            }else{
                console.log("上下班没有数据");
            }

        }
    })
})


//刘锦昌写的，小程序获取会议通知的接口
myroute.get('/memories1', (req, res) => {
    console.log("进入后台了哦，会议通知的接口");
    const sql = `SELECT
    meeting.meeting_num,admin.name,department.department_name,postadmin.post_name,meeting.meeting_todotime,meeting.meeting_tit,
    meeting.meeting_cont,meetingroom.metroom_name,meeting.meeting_startime,meeting.meeting_endtime,meetingroom.metroom_standard,
    meetingroom.metroom_capacity,meeting.meeting_pernum
    FROM meeting
    LEFT JOIN admin ON admin.work_id = meeting.meeting_per
    LEFT JOIN postadmin ON admin.jobs = postadmin.post_id
    LEFT JOIN department ON department.id = postadmin.post_department
    LEFT JOIN meetingroom ON meeting.meeting_room = meetingroom.metroom_id `;
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err);
        } else {
            if(data){
                console.log("会议通知有数据");
                res.send({ code: 200, data: data })
            }else{
                console.log("会议通知没有数据");
            }
        }
    })
})

//刘锦昌写的，小程序获取公告通知
myroute.get('/notice_home', (req, res) => {
    console.log("进入后台了哦，公告通知的接口");
    const sql = ` SELECT notice.notice_id,notice_author,notice.notice_time,notice.notice_content,notice.notice_title,notice.digest,
    notice.top,notice.tag,notice_class.notice_className,notice_state.notice_stateName FROM notice
    LEFT JOIN notice_class ON notice.notice_class = notice_class.notice_classId
    LEFT JOIN notice_state ON notice.notice_state = notice_state.notice_stateId;
     `;
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err);
        } else {
            if(data){
                console.log("公告通知有数据");
                res.send({ code: 200, data: data })
            }else{
                console.log("公告通知没有数据");
            }

        }
    })
})




//小程序获取申请
myroute.get('/get_shenqing', (req, res) => {
    const sql = `SELECT COUNT(*) AS shenqin FROM application WHERE apply_name='${req.query.account}' AND state='待审核';`;
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err);
        } else {
            res.send({ code: 200, data: data })
        }
    })
})
//小程序获取考勤
myroute.get('/get_kaoqin', (req, res) => {
    console.log("work_id是什么：" + req.query.word_id);
    const sql = `    SELECT * FROM clock 
    LEFT JOIN status_table ON clock.state=status_table.id
    WHERE word_id='${req.query.word_id}';`;
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err);
        } else {
            // res.send({ code: 200, data: data })
            if(data){
                console.log("考勤有数据2");
                res.send({ code: 200, data: data })
            }else{
                console.log("考勤没有数据2");
            }

        }
    })
})
//小程序获取申请表信息
myroute.get('/get_shenqingjilu', (req, res) => {
    const sql = `SELECT * FROM application
    LEFT JOIN application_type ON application.type=application_type.id
    WHERE agree='同意' AND apply_name='${req.query.account}';`;
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err);
        } else {
            res.send({ code: 200, data: data })
        }
    })
})
//小程序获取会议信息
myroute.get('/get_huiyijilu', (req, res) => {
    const sql = `SELECT COUNT(*) AS huiyi FROM meeting WHERE meeting_per='${req.query.account}';`;
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err);
        } else {
            res.send({ code: 200, data: data })
        }
    })
})
//小程序获取打卡记录
myroute.get('/get_dakajilu', (req, res) => {
    const sql = `SELECT * FROM clock WHERE word_id='${req.query.word_id}' AND clock_in LIKE '${req.query.time}%';`;
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err);
        } else {
            res.send({ code: 200, data: data })
        }
    })
})
//小程序打卡
// myroute.post('/post_dakajilu', (req, res) => {
//     const sql = `INSERT INTO clock VALUES('${req.body.word_id}','${req.body.time}',NULL,'${req.body.state}',NULL);`;
//     mySql.query(sql, (err, data) => {
//         if (err) {
//             console.log(err);
//         } else {
//             res.send({ code: 200, msg: '打卡成功' })
//         }
//     })
// })
myroute.get('/edit_dakajilu', (req, res) => {
    // const sql = `UPDATE clock SET clock_go='${req.query.time}' WHERE word_id='${req.query.word_id}' AND clock_in='${req.query.time_in}';`;
    const sql = req.query.sql
    console.log(sql)
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err);
        } else {
            res.send({ code: 200, msg: '打卡成功' })
        }
    })
})
//图片上传的接口
myroute.post('/upload_touxiang', upload.single('file'), (req, res) => {
    // const sql = `UPDATE clock SET clock_go='${req.query.time}' WHERE word_id='${req.query.word_id}' AND clock_in='${req.query.time_in}';`;
    // const sql = req.query.sql
    // console.log(sql)
    // mySql.query(sql, (err, data) => {
    //     if (err) {
    //         console.log(err);
    //     } else {
    //         res.send({ code: 200, msg: '打卡成功' })
    //     }
    // })
    res.send(req.file.filename);
})
myroute.post('/gettouxiang', function (req, res, next) {
    // console.log(req.body.imgName)
    let sql = `UPDATE admin SET figure='${req.body.imgName}' WHERE work_id='${req.body.work_id}';`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err);
            return res.json({
                code: 500,
                msg: '添加数据失败，请稍后重试'
            })
        }
        res.json({
            code: 200,
            msg: '添加数据成功',
            data: data
        })
    })
})
//小程序修改邮箱电话号码
myroute.post('/edit_Msg', (req, res) => {
    // const sql = `UPDATE clock SET clock_go='${req.query.time}' WHERE word_id='${req.query.word_id}' AND clock_in='${req.query.time_in}';`;
    const sql = req.body.sql
    console.log(sql)
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err);
        } else {
            res.send({ code: 200, msg: '修改成功' })
        }
    })
})
module.exports = myroute